﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NetDon.Utilities;
using NetDon.eBoss.Data.EntityClasses;

namespace NetDon.eBoss.Portal
{
    //http://www.cnblogs.com/cnaspnet/archive/2011/04/22/2025123.html
    public class DataImportService
    {
        private const int COLUMN_RECEIVINGORDER_DATE = 8;


        private static void RemoveEmptyRows(DataTable table)
        {
            for (int i = table.Rows.Count - 1; i >= 0; i--)
            {
                DataRow row = table.Rows[i];
                if (!new Func<bool>(delegate
                                        {
                                            for (int j = 0; j < table.Columns.Count; j++)
                                            {
                                                if (!IsBlankData(row[j]))
                                                {
                                                    return true;
                                                }
                                            }
                                            return false;
                                        })())
                {
                    table.Rows.RemoveAt(i);
                }
            }
        }

        private static void RemoveEmptyColumns(DataTable table)
        {
            for (int i = table.Columns.Count - 1; i >= 0; i--)
            {
                if (!new Func<bool>(delegate
                                        {
                                            for (int j = 0; j < table.Rows.Count; j++)
                                            {
                                                if (!IsBlankData(table.Rows[j][i]))
                                                {
                                                    return true;
                                                }
                                            }
                                            return false;
                                        })())
                {
                    table.Columns.RemoveAt(i);
                }
            }
        }

        private static bool IsBlankData(object value)
        {
            if (MiscUtilities.IsNullOrEmpty(value))
            {
                return true;
            }
            if (value is string)
            {
                return string.IsNullOrEmpty(Convert.ToString(value).Trim());
            }
            return false;
        }

        public static void Export(string fileName,DataTable dt)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
            NPOI.SS.UserModel.Sheet sheet = book.CreateSheet("Product");
            NPOI.SS.UserModel.Row row = sheet.CreateRow(0);
 
            //创建表头
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
            }
 
            //创建内容
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                NPOI.SS.UserModel.Row row2 = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                    row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
            }
 
 
            //写入到客户端
            FileStream file = new FileStream(@fileName, FileMode.Create);
            book.Write(file);
            file.Close();
            book.Dispose();
        }


        /// <summary>
        /// Reads data from excel file.
        /// </summary>
        /// <param name="fileLocation">The excel file location.</param>
        /// <returns></returns>
        public static IList<ProductgroupEntity> ReadExcelData(string fileLocation)
        {
            var data = new List<ProductgroupEntity>();
            using (var file = new FileStream(fileLocation, FileMode.Open, FileAccess.Read))
            {
                var hssfworkbook = new HSSFWorkbook(file);
                Sheet s = hssfworkbook.GetSheetAt(0);
                int beginRow = 0;

                for (int i = beginRow + 1; i <= s.LastRowNum; i++)
                {
                    Cell c1 = s.GetRow(i).GetCell(1); //商品编号 
                    if (c1 != null && !string.IsNullOrWhiteSpace(c1.ToString()))
                    {
                        Cell c0 = s.GetRow(i).GetCell(0); //标签
                        Cell c2 = s.GetRow(i).GetCell(2); //商品名称
                        Cell c3 = s.GetRow(i).GetCell(3); //规格

                        Cell c4 = s.GetRow(i).GetCell(4); //库存量

                        Cell c5 = s.GetRow(i).GetCell(5); //一口价


                        data.Add(new ProductgroupEntity
                                     {
                                  
                                         Productcode = c1.ToString(),
                                         Productname = c2.ToString(),
                                         Size = c3.ToString(),
                                         Retailprice =c5.ToString(),
                                         Quantity =c4.ToString().ToInt32()


                                     }
                            );
                    }
                }
            }
            return data;
        }

    }
}